package com.csvw.skr.newTask.service;

import com.csvw.skr.basic.core.JobType;
import com.csvw.skr.basic.entity.Dealer;
import com.csvw.skr.basic.entity.User;
import com.csvw.skr.newTask.core.VisitItem;
import com.csvw.skr.newTask.core.VisitMissionReportDto;
import com.csvw.skr.newTask.core.VisitPlanDto;
import com.csvw.skr.newTask.entity.PlanProblem;
import com.csvw.skr.newTask.entity.visit.Mission;
import com.csvw.skr.newTask.entity.visit.PlanMission;
import com.csvw.skr.newTask.entity.visit.VisitPlan;
import com.csvw.skr.notice.entity.NoticePush;
import com.google.common.collect.Lists;
import com.webonn.frame.core.Pagination;
import com.webonn.frame.db.MyDao;
import com.webonn.frame.util.IdGenerator;
import com.webonn.frame.util.JavaBeanToMap;
import com.webonn.frame.util.MyStringUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.Id;
import javax.validation.constraints.NotNull;
import javax.ws.rs.core.MultivaluedMap;
import javax.ws.rs.core.Response;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class VisitService {
    private static final Logger LOGGER = LoggerFactory.getLogger(VisitService.class);
    @Autowired
    private MyDao myDao;
    /**
     * 根据任务类型查询
     * @param type
     * @return
     */
    public List<Map<String,Object>> queryTaskByType(int type){
        String sql = "select pk_id as id,name,description,type,checklist from csvw_skr_task_visit_mission where type = ?";
        //把查询结果转化为Map集合
        return myDao.queryResultToMap(sql,new Object[]{type});
    }

    /**
     * 根据missionId获取检查项
     * @param missionId
     * @return
     */
    public List<Map<String,Object>> getMissionCheckList(String missionId){
        //mysql中对varchar类型排序需要点,查询
        String sql = "select pk_id as id,name,description from csvw_skr_task_visit_check where active = '1' and missionid = ? order by cast(pk_id as signed)";
        return  myDao.queryResultToMap(sql,new Object[]{missionId});
    }

    /**
     * type=3  表示自定义类型任务
     * 创建自定义任务项
     * @param mission
     */
    public Mission addMission(Mission mission){
        String publisher = mission.getPublisher();
        User user = myDao.get(publisher,User.class);
        //判断只有商务经理才能创建自定义类型任务
        if(JobType.BUSINESS_MANAGER.equalsIgnoreCase(user.getJob())){
            mission.setType(3);
            mission.setChecklist(false);
        }
        Date now = new Date();
        mission.setCreated(now);
        mission.setUpdated(now);
        //设置id自动生成方式
        String id = IdGenerator.generateTimeStampId("mission"+mission.getPublisher(),now);
        mission.setId(id);
        mission.setActive(true);
        return myDao.persist(mission);
    }

    /**
     * 修改任务
     * @param entity
     * @return
     */
    public Mission modifyMission(Mission entity){
        Mission oldMission = myDao.get(entity.getId(),Mission.class);
        oldMission.setName(entity.getName());
        oldMission.setDescription(entity.getDescription());
        oldMission.setUpdated(new Date());
        return myDao.persist(oldMission);
    }

    /**
     * 删除自定义项任务
     * @param missionId
     * @return
     */
    public  Mission deleteMission(String missionId){
        Mission mission = myDao.get(missionId,Mission.class);
        mission.setActive(false);
        return  myDao.persist(mission);
    }

    /**
     * 创建售前店访计划接口
     * @param visitPlanDto
     * @return
     */
    public VisitPlan createVisitPlan(@NotNull VisitPlanDto visitPlanDto){
        VisitPlan visitPlan = visitPlanDto.getVisitPlan();
        Session session = myDao.openSession();
        //开启事务
        Transaction tx = session.beginTransaction();
        try {
            visitPlan.setActive(true);
            Date now = new Date();
            visitPlan.setCreated(now);
            visitPlan.setUpdated(now);
            visitPlan.setId(IdGenerator.generateDateTimeId("visit_"+visitPlan.getBusinessManager(),now));
            //店访计划的地点默认为经销商地址
            if (MyStringUtil.isNullOrEmpty(visitPlan.getDealer())){
                Dealer dealer = session.get(Dealer.class,visitPlan.getDealer());
                visitPlan.setAddress(dealer.getShorter());
            }
            Serializable visit = session.save(visitPlan);
            List<String> missionIds = visitPlanDto.getMissionIds();
            if(missionIds != null){
                for (String missionId: missionIds){
                    PlanMission planMission = new PlanMission();
                    String planMissionId = IdGenerator.generateMd5Id("",visit+missionId);
                    planMission.setId(planMissionId);
                    planMission.setVisitId(visit+"");
                    planMission.setMissionId(missionId);
                    planMission.setFinish(0);
                    session.save(planMission);
                }
            }
            //创建了计划，检查是否有检查表的任务，如果则初始化到csvw_skr_task_visit_mission_check
            if(visitPlan.getStatus() == 21){
                String sql = "select t1.pk_id from csvw_skr_task_visit_mission t1 where t1.checklist = true   ";
                List<String> items = session.createQuery(sql).list();
                if(items != null && items.size() > 0){
                    items.retainAll(missionIds);//retainAll取两个集合的交集
                    if(items.size() > 0){
                        for (String item:items){
                            String ide_pre = visit + "_" + item + "_";
                            String insert_sql = "insert into csvw_skr_task_visit_mission_check " +
                                    "(pk_id, checkId, visitId, missionId, pass) select concat(?, pk_id) as pk_id," +
                                    " pk_id as checkId, ? as visitId, missionId, false as pass " +
                                    "from csvw_skr_task_visit_check where missionId = ? ";
                            SQLQuery q = session.createSQLQuery(insert_sql);
                            q.setParameter(0,ide_pre);
                            q.setParameter(1,visit);
                            q.setParameter(2,item);
                            q.executeUpdate();
                        }
                    }
                }
            }
            tx.commit();
            return visitPlan;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 更新售前店访计划
     * @param visitPlanDto
     * @return
     */
    public VisitPlan updateVisitPlan(@NotNull VisitPlanDto visitPlanDto){
        VisitPlan visitPlan = visitPlanDto.getVisitPlan();
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try{
            VisitPlan old = myDao.get(visitPlan.getId(),VisitPlan.class);
            if (old == null) return  null;
            visitPlan.setActive(old.isActive());
            visitPlan.setCreated(old.getCreated());
            visitPlan.setUpdated(new Date());
            visitPlan.setRbo(old.getRbo());
            visitPlan.setCommercial(old.getCommercial());
            visitPlan.setArea(old.getArea());
            visitPlan.setBusinessManager(old.getBusinessManager());
            String dealerId = visitPlan.getDealer();
            //店访计划的地点随着经销商改变而改变
            if (!MyStringUtil.isNullOrEmpty(dealerId) && !dealerId.equalsIgnoreCase(old.getDealer())) {
                Dealer dealer = session.get(Dealer.class, dealerId);
                visitPlan.setAddress(dealer.getShorter());
            } else {
                visitPlan.setAddress(old.getAddress());
            }
            session.update(visitPlan);
            //删除原来店访计划走访项关联表
            String visit = visitPlan.getId();
            String delete_sql = "delete t1 from csvw_skr_task_visit_plan_mission t1 where t1.visitId = ?   ";
            SQLQuery q = session.createSQLQuery(delete_sql);
            q.setParameter(0, visit);
            boolean result = q.executeUpdate() >= 0;
            //如果删除成功
            if(result){//保存店访计划走访项关联表
                List<String> missionIds = visitPlanDto.getMissionIds();
                for (String missionId: missionIds){
                    PlanMission planMission = new PlanMission();
                    String planMissionId = IdGenerator.generateMd5Id("",visit+missionId);
                    planMission.setId(planMissionId);
                    planMission.setVisitId(visit+"");
                    planMission.setMissionId(missionId);
                    planMission.setFinish(0);
                    session.save(planMission);
                }
                //创建了计划，检查是否有检查表的任务，如果则初始化到csvw_skr_task_visit_mission_check
                if(visitPlan.getStatus() == 21){
                    String sql = "select t1.pk_id from csvw_skr_task_visit_mission t1 where t1.checklist = true   ";
                    List<String> items = session.createQuery(sql).list();
                    if(items != null && items.size() > 0){
                        items.retainAll(missionIds);//retainAll取两个集合的交集
                        if(items.size() > 0){
                            for (String item:items){
                                String ide_pre = visit + "_" + item + "_";
                                String insert_sql = "insert into csvw_skr_task_visit_mission_check " +
                                        "(pk_id, checkId, visitId, missionId, pass) select concat(?, pk_id) as pk_id," +
                                        " pk_id as checkId, ? as visitId, missionId, false as pass " +
                                        "from csvw_skr_task_visit_check where missionId = ? ";
                                SQLQuery insert_q = session.createSQLQuery(insert_sql);
                                insert_q.setParameter(0,ide_pre);
                                insert_q.setParameter(1,visit);
                                insert_q.setParameter(2,item);
                                insert_q.executeUpdate();
                            }
                        }
                    }
                }

            }
            tx.commit();
            return visitPlan;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 查询店访计划
     * @param userId
     * @param start
     * @param limit
     * @param filter
     * @return
     */
    public Pagination queryVisitPlan(String userId, int start, int limit, MultivaluedMap<String,String> filter){
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);

        User queryUser = myDao.get(userId,User.class);
        String sql = "SELECT plan.pk_id as id, plan.`name`, dealer.`name` as dealer, plan.startTime, plan.endTime, plan.`status`, u.name as businessManager, " +
                "( SELECT COUNT(planMission.pk_id) FROM csvw_skr_task_visit_plan_mission planMission WHERE planMission.visitId = plan.pk_id ) AS planMissionNum " +
                "FROM csvw_skr_task_visit_plan plan " +
                "LEFT JOIN csvw_skr_basic_dealer dealer ON plan.dealer = dealer.pk_code " +
                "LEFT JOIN csvw_skr_basic_user u ON u.pk_empno = plan.businessManager " +
                "WHERE plan.active = TRUE %s ORDER BY plan.updated DESC";

        List<Object> paramsList = Lists.newArrayList();
        //根据用户职位加入相应的搜索条件   //查询条件
        String query_term = buildVisitPlanUserTerm(paramsList, queryUser) + buildVisitPlanQueryTerm(paramsList, filter);
        return myDao.queryResultToMapPagination(String.format(sql, query_term), p, paramsList.toArray());
    }

    /**
     * 根据用户岗位添加查询条件
     * @param paramsList
     * @param queryUser
     * @return
     */
    private String buildVisitPlanUserTerm(List<Object> paramsList, User queryUser) {
        String query_term = "";
        if (JobType.BUSINESS_MANAGER.equalsIgnoreCase(queryUser.getJob())) {//商务经理
            //107只能看自己的
            query_term += " and plan.businessManager = ? ";
            paramsList.add(queryUser.getEmpno());
        } else if (JobType.COMMERCIAL_DIRECTOR.equalsIgnoreCase(queryUser.getJob()) || JobType.SALES_VICE_MANAGER.equalsIgnoreCase(queryUser.getJob())) {
            //商务总监、销售副经理
            query_term += " and plan.commercial = ? and plan.status>=21 ";
            paramsList.add(queryUser.getOrganization());
        } else if (JobType.RBO_GENERAL_MANAGER.equalsIgnoreCase(queryUser.getJob())) {
            //RBO总经理
            query_term += " and plan.rbo = ? and plan.status >= 21 ";
            paramsList.add(queryUser.getOrganization());
        } else {
            query_term += " and plan.status >= 21 ";
        }
        return query_term;
    }

    /**
     *
     * @param paramsList
     * @param filter
     * @return
     */
    private String buildVisitPlanQueryTerm(List<Object> paramsList, MultivaluedMap<String, String> filter) {
        String query_term = "";
        String search = filter.getFirst("search");
        if (!MyStringUtil.isNullOrEmpty(search)) {
            query_term += " and CONCAT_WS(',', plan.rbo, plan.commercial, plan.area, plan.dealer, plan.name, u.name) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }

        String startTime = filter.getFirst("startTime");
        if (MyStringUtil.nonNullOrEmpty(startTime)) {
            query_term += " and plan.startTime >= ? ";
            paramsList.add(new Date(Long.valueOf(startTime)));
        }

        String endTime = filter.getFirst("endTime");
        if (MyStringUtil.nonNullOrEmpty(startTime)) {
            query_term += " and plan.endTime <= ? ";
            paramsList.add(new Date(Long.valueOf(endTime)));
        }
        return query_term;
    }

    /**
     * 获取售前店访计划对象
     *
     * @param visitId
     * @return
     */
    public VisitPlan getVisitPlan(@NotNull String visitId) {
        return myDao.get(visitId, VisitPlan.class);
    }

    /**
     * 删除计划
     * @param visitId
     * @return
     */
    public boolean deleteVisitPlan(@NotNull String visitId){
        Session session = myDao.openSession();
        String delete_sql = "delete t1, t2 from csvw_skr_task_visit_plan t1 " +
                "left JOIN csvw_skr_task_visit_plan_mission t2 on t2.visitId = t1.pk_id " +
                " where t1.pk_id = ?   ";
        SQLQuery q = session.createSQLQuery(delete_sql);
        q.setParameter(0, visitId);
        boolean result = q.executeUpdate() >= 0;
        if(session != null){
            session.close();
        }
        return result;
    }

    /**
     * 撤回售前店访报告
     * @param visitId
     * @return
     */
    public boolean revocationVisitReport(String visitId){
        Session session = myDao.openSession();
        Transaction transaction = session.beginTransaction();
        try {
            //删除店访走访项检查项关联表
            String deletePlanMissionCheckSql = "delete from csvw_skr_task_visit_mission_check where visitId = ?";
            //删除店访问题表
            String deleteProblemSql = "delete from csvw_skr_task_problem where planId = ?";
            // 更新
            String updatePlanMissionSql = "update csvw_skr_task_visit_plan_mission SET finish = 0 AND pics = null AND problemId = null AND resultDescription = null  WHERE visitId = ? ";
            //更新店访计划状态
            String updatePlanSql = "update csvw_skr_task_visit_plan SET status = 11 WHERE pk_id = ?";

            boolean deleteResult1 = myDao.bindSQLQueryParams(session.createSQLQuery(deletePlanMissionCheckSql),new Object[]{visitId}).executeUpdate() >= 0;
            boolean deleteResult2 = myDao.bindSQLQueryParams(session.createSQLQuery(deleteProblemSql),new Object[]{visitId}).executeUpdate()>=0;
            boolean updateResult1 = myDao.bindSQLQueryParams(session.createSQLQuery(updatePlanMissionSql), new Object[]{visitId}).executeUpdate() >= 0;
            boolean updateResult2 = myDao.bindSQLQueryParams(session.createSQLQuery(updatePlanSql), new Object[]{visitId}).executeUpdate() >= 0;

            transaction.commit();
            return deleteResult1 && deleteResult2 && updateResult1 && updateResult2;
        }catch (Exception e){
            e.printStackTrace();
            transaction.rollback();
            return false;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 查询店访报告列表
     * @param userId
     * @param start
     * @param limit
     * @param filter
     * @return
     */
    public Pagination queryVisitReport(String userId,int start,int limit,MultivaluedMap<String,String> filter){
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        //获取查询的用户
        User queryUser = myDao.get(userId, User.class);
        String totalSql = "SELECT  count(1) FROM csvw_skr_task_visit_plan plan " +
                "LEFT JOIN csvw_skr_basic_user u ON u.pk_empno = plan.businessManager " +
                "LEFT JOIN csvw_skr_basic_dealer dealer ON plan.dealer = dealer.pk_code " +
                "WHERE plan.active = TRUE ";

        String sql = "SELECT plan.pk_id as id, plan.`name`, dealer.`name` as dealer, plan.startTime, plan.endTime, plan.`status`, u.name as businessManager, " +
                "( SELECT COUNT(planMission.pk_id) FROM csvw_skr_task_visit_plan_mission planMission WHERE planMission.visitId = plan.pk_id ) AS planMissionNum, " +
                "( SELECT COUNT(problem.pk_id) FROM csvw_skr_task_problem problem WHERE problem.planId = plan.pk_id ) AS problemNum " +
                "FROM csvw_skr_task_visit_plan plan " +
                "LEFT JOIN csvw_skr_basic_user u ON u.pk_empno = plan.businessManager " +
                "LEFT JOIN csvw_skr_basic_dealer dealer ON plan.dealer = dealer.pk_code " +
                "WHERE plan.active = TRUE  ORDER BY plan.updated DESC";
        List<Object> paramsList = Lists.newArrayList();
        //根据用户职位加入相应的搜索条件，查询条件
        String query_term = buildVisitReportUserTerm(paramsList, queryUser) + buildVisitPlanQueryTerm(paramsList, filter);

        BigInteger b = (BigInteger) myDao.uniqueResult(String.format(totalSql, query_term), paramsList.toArray());

        return myDao.queryResultToMapPagination(String.format(sql, query_term), p, paramsList.toArray(), b.intValue());
    }

    public String  buildVisitReportUserTerm(List<Object> paramsList,User queryUser){
        String query_term = "";
        if (JobType.BUSINESS_MANAGER.equalsIgnoreCase(queryUser.getJob())) {
            query_term += " and plan.businessManager = ? and plan.status>=21";
            paramsList.add(queryUser.getEmpno());
        } else if (JobType.COMMERCIAL_DIRECTOR.equalsIgnoreCase(queryUser.getJob()) || JobType.SALES_VICE_MANAGER.equalsIgnoreCase(queryUser.getJob())) {
            query_term += " and plan.commercial = ? and plan.status>=31 ";
            paramsList.add(queryUser.getOrganization());
        } else if (JobType.RBO_GENERAL_MANAGER.equalsIgnoreCase(queryUser.getJob())) {
            query_term += " and plan.rbo = ? and plan.status >= 31 ";
            paramsList.add(queryUser.getOrganization());
        } else {
            query_term += " and plan.status >= 31 ";
        }
        return query_term;
    }

    /**
     * 审批售前店访报告
     * @param visitId
     * @param desc
     * @param pass
     * @return
     */
    public VisitPlan approvalVisitReport(String visitId,String desc,boolean pass){
        VisitPlan old = myDao.get(visitId,VisitPlan.class);
        //设置审批结果值
        old.setApprovalDescription(desc);
        //消息通知
        NoticePush noticePush = new NoticePush();
        noticePush.setTitle("店访报告审批结果");
        noticePush.setCreated(new Date());
        noticePush.setObjectId(old.getId());
        noticePush.setReceiver(old.getBusinessManager());
        noticePush.setType("VisitResult");

        if(pass){
            old.setStatus(41);
            noticePush.setContent(old.getName()+"报告通过");
        }else {
            old.setStatus(22);
            noticePush.setContent(old.getName()+"报告被驳回");
        }
         VisitPlan result = myDao.persist(old);
        myDao.persist(noticePush);
        return result;
    }

    /**
     * 获取店访报告详情
     * @param visitId
     * @return
     */
    public Map<String,Object> getVisitReportInfo(String visitId){
        //获取报告基本信息
        Map<String,Object> result = new HashMap<>();
        String sql = "select plan.pk_id as id, plan.name, plan.startTime, plan.endTime, plan.dealer, plan.approvalDescription,plan.status, " +
                "dealer.shorter as dealerName " +
                "from csvw_skr_task_visit_plan plan " +
                "left join csvw_skr_basic_dealer dealer on dealer.pk_code = plan.dealer " +
                "where plan.pk_id=? ";
        List<Map<String,Object>> resultToMap = myDao.queryResultToMap(sql,new Object[]{visitId});
        if(resultToMap == null || resultToMap.size() == 0) return null;
        result.put("visitReport",resultToMap.get(0));

        String dealer = (String) resultToMap.get(0).getOrDefault("dealer","");
        //上次走访项问题
        if (dealer.length() > 0){
            String last_missions_sql = "select mission.pk_id as id, mission.name, mission.description, mission.type, mission.checklist, problem.planId, problem.checkPlanId, plan_mission.finish as finish from csvw_skr_task_visit_mission mission " +
                    "left join csvw_skr_task_problem problem on problem.missionId = mission.pk_id " +
                    "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.missionId = mission.pk_id " +
                    "where problem.pass !=1 and plan_mission.finish !=1 and plan_mission.visitId = problem.planId and plan_mission.missionId = problem.missionId and problem.dealer=? and problem.planId != ? ";
            List<Map<String,Object>> lastReview = myDao.queryResultToMap(last_missions_sql,new Object[]{dealer,visitId});

            if(lastReview == null || lastReview.size() < 1){
                lastReview = Lists.newArrayList();
            }
            //加上本次已经解决的其他问题部分
            String check_last_problem_sql = "select mission.pk_id as id, mission.name, mission.description, mission.type, mission.checklist, problem.planId, problem.checkPlanId, problem.pass as finish from csvw_skr_task_visit_mission mission " +
                    "left join csvw_skr_task_problem problem on problem.missionId = mission.pk_id " +
                    "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.missionId = mission.pk_id " +
                    "where problem.pass =1 and plan_mission.finish =1 and plan_mission.visitId = problem.planId and plan_mission.missionId = problem.missionId and problem.dealer=? and problem.planId != ? and problem.checkPlanId = ?  ";
            List<Map<String, Object>> checked_problems = myDao.queryResultToMap(check_last_problem_sql, new Object[]{dealer, visitId, visitId});

            if (null != checked_problems && checked_problems.size() > 0) {
                lastReview.addAll(checked_problems);
            }

            result.put("lastReview", lastReview);
        }else {
            result.put("lastReview",Lists.newArrayList());
        }
        //获取本次走访项任务
        String missions_sql = "select mission.pk_id as id, mission.name, mission.description, mission.type, mission.checklist, plan_mission.visitId as planId, plan_mission.finish from csvw_skr_task_visit_mission mission " +
                "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.missionId = mission.pk_id " +
                "where mission.active = true and plan_mission.visitId=? order by mission.created asc, mission.pk_id asc ";
        List<Map<String, Object>> missions = myDao.queryResultToMap(missions_sql, new Object[]{visitId});


        if (null == missions || missions.size() < 1) {
            missions = Lists.newArrayList();
        }

        result.put("missions", missions);

        return result;
    }

    /**
     * 提交售前店访任务
     * @param visitId
     * @return
     */
    public VisitPlan submtVisitReport(String visitId){
        VisitPlan old = myDao.get(visitId,VisitPlan.class);
        old.setStatus(31);

        return  myDao.persist(old);
    }

    /**
     * 店访报告中创建自定义走访项
     * @param visitItem
     * @return
     */
    public PlanMission createPlanItem(@NotNull VisitItem visitItem){
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try {
            //新增走方项任务
            Mission mission = new Mission();
            Date now = new Date();
            mission.setActive(true);
            mission.setActive(true);
            mission.setCreated(now);
            mission.setUpdated(now);
            String missionId = IdGenerator.generateDateTimeId("mission_"+visitItem.getPublisher(),now);
            mission.setId(missionId);
            mission.setName(visitItem.getName());
            mission.setType(9);//自添加其他问题
            mission.setChecklist(false);
            session.persist(mission);

            String visitId = visitItem.getPlanId();
            PlanMission planMission = new PlanMission();
            planMission.setFinish(visitItem.getFinish());
            planMission.setVisitId(visitId);
            planMission.setMissionId(missionId);
            planMission.setPics(visitItem.getPics());
            planMission.setResultDescription(visitItem.getResultDescription());
            String planMissionId = IdGenerator.generateMd5Id("",visitId+missionId);
            planMission.setId(planMissionId);

            PlanProblem problem = visitItem.getPlanProblem();
            if(visitItem.getFinish() != 1 && problem != null){
                String problemId = IdGenerator.generateMd5Id("problem",visitId + missionId);
                problem.setId(problemId);
                problem.setPass(0);//设置未通过，待跟进
                session.saveOrUpdate(problem);
                planMission.setProblemId(problem.getId());
            }
            session.saveOrUpdate(planMission);
            tx.commit();
            return planMission;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 处理上次走访项问题
     * @param problemId
     * @param checkResult
     * @param checkPlanId
     * @param pass
     * @return
     */
    public PlanProblem checkPreviousProblem(String problemId,String checkResult,String checkPlanId,int pass){
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try {
            PlanProblem problem = session.get(PlanProblem.class,problemId);
            problem.setCheckResult(checkResult);
            problem.setPass(pass);
            problem.setCheckTime(new Date());
            session.saveOrUpdate(problem);

            if(pass == 1){//完成
                String sql = "update csvw_skr_task_visit_plan_mission t1 set t1.finish = 1 where t1.visitId = ? and t1.missionId = ?";
                SQLQuery q = session.createSQLQuery(sql);
                q.setParameter(0,problem.getPlanId());
                q.setParameter(1,problem.getMissionId());
                q.executeUpdate();
            }else {
                String sql = "update csvw_skr_task_visit_plan_mission t1 set t1.finish = 2 where t1.visitId = ? and t1.missionId = ?";
                SQLQuery q = session.createSQLQuery(sql);
                q.setParameter(0,problem.getPlanId());
                q.setParameter(1,problem.getMissionId());
                q.executeUpdate();
            }

            tx.commit();
            return problem;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 查看售前店访任务详情
     * @param visitId
     * @param missionId
     * @return
     */
    public Map<String,Object> getMissionInfo(String visitId,String missionId){
        Map<String,Object> result = new HashMap<>();
        //获取任务信息
        String mission_sql = "select mission.pk_id as id, mission.name, mission.description, mission.type, mission.checklist,plan_mission.finish, plan_mission.pics, plan_mission.resultDescription from csvw_skr_task_visit_mission mission " +
                "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.missionId = mission.pk_id " +
                "where plan_mission.visitId = ? and plan_mission.missionId = ?  ";
        List<Map<String,Object>> missionInfo = myDao.queryResultToMap(mission_sql,new Object[]{visitId,missionId});
        if(null != missionInfo && missionInfo.size() > 0){
            result.put("missionInfo",missionInfo.get(0));
        }else {
            return null;
        }
        //获取检查表
        if (missionInfo.get(0).get("checklist").toString() == "1" || missionInfo.get(0).get("checklist").toString() == "true") {
            String sql = "select visit_check.pk_id as id, name, description, mission_check.pass from csvw_skr_task_visit_check visit_check " +
                    "left join csvw_skr_task_visit_mission_check mission_check on mission_check.checkId = visit_check.pk_id " +
                    "where mission_check.visitId=?  and mission_check.missionId =? order by  cast(visit_check.pk_id as signed) asc ";
            List<Map<String, Object>> checklistresult = myDao.queryResultToMap(sql, new Object[]{visitId, missionId});
            if(null != checklistresult && checklistresult.size() > 0){
                result.put("checks",checklistresult);
            }else {
                result.put("checks",Lists.newArrayList());
            }
        }else {
            result.put("checks",Lists.newArrayList());
        }
        //获取可参考检查项信息
        String mission_indices_sql = "select indices.pk_id as id, name, mission_indices.required from csvw_skr_task_visit_indices indices " +
                "left join csvw_skr_task_visit_mission_indices mission_indices on mission_indices.indicesId = indices.pk_id " +
                "where mission_indices.missionId = ?  ";
        List<Map<String,Object>> missionIndices = myDao.queryResultToMap(mission_indices_sql,new Object[]{missionId});
        if(null != missionIndices && missionIndices.size() > 0){
            result.put("missionIndices",missionIndices);
        }else {
            result.put("missionIndices",Lists.newArrayList());
        }
        //获取检查项问题信息
        String mission_problem_sql = "SELECT problem.pk_id AS id, problem.checkResult, problem.planId, problem.missionId, problem.dealer, problem.pics, problem.problemDescription, problem.problemAnalysis, problem.improvementMeasures, problem.achieveTarget, problem.charger, problem.finishTime, problem.pass" +
                " from csvw_skr_task_problem problem " +
                "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.problemId = problem.pk_id " +
                "where plan_mission.visitId = ? and plan_mission.missionId = ?  ";
        List<Map<String, Object>> missionProblems = myDao.queryResultToMap(mission_problem_sql, new Object[]{visitId, missionId});
        if (null != missionProblems && missionProblems.size() > 0) {
            result.put("missionProblem", missionProblems.get(0));
        } else {
            result.put("missionProblem", null);
        }
        return result;
    }

    /**
     * 提交(添加)售前店访任务报告
     * @param visitMissionReportDto
     * @return
     */
    public  PlanMission newPlanMissionReport(VisitMissionReportDto visitMissionReportDto){
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try{
            PlanMission planMission = visitMissionReportDto.getPlanMission();
            PlanProblem problem = visitMissionReportDto.getPlanProblem();
            String planId = planMission.getVisitId();
            String missionId = planMission.getMissionId();
            String planMissionId = IdGenerator.generateMd5Id("",planId + missionId);
            planMission.setId(planMissionId);
            if(problem != null){
                String problemId = IdGenerator.generateMd5Id("problem",planId+missionId);
                problem.setId(problemId);
                problem.setPass(0);
                session.saveOrUpdate(problem);
                planMission.setProblemId(problem.getId()+"");
            }
            session.saveOrUpdate(planMission);

            Mission mission = session.get(Mission.class,missionId);
            if(mission.isChecklist()){//如果有检查项
                List<String> checkIds = visitMissionReportDto.getCheckIds();
                if(checkIds != null && checkIds.size() > 0){
                    String initSql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = false where t1.visitId = ? and t1.missionId = ?";
                    SQLQuery ql = session.createSQLQuery(initSql);
                    ql.setParameter(0,planId);
                    ql.setParameter(1,missionId);
                    ql.executeUpdate();

                    int length = checkIds.size();
                    for (int i = 0;i < length;i++){
                        String sql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = true where t1.checkId = ? and t1.visitId = ? and t1.missionId = ?";
                        SQLQuery q = session.createSQLQuery(sql);
                        q.setParameter(0,checkIds.get(i).trim());
                        q.setParameter(1,planId);
                        q.setParameter(2,missionId);
                        q.executeUpdate();
                    }
                }
            }
            tx.commit();
            return planMission;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    /**
     * 更新店访任务售前报告
     * @param visitMissionReportDto
     * @return
     */
    public PlanMission updatePlanMissionReport(VisitMissionReportDto visitMissionReportDto){
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try {
            PlanMission planMission = visitMissionReportDto.getPlanMission();
            PlanProblem problem = visitMissionReportDto.getPlanProblem();
            String planId = planMission.getVisitId();
            String missionId = planMission.getMissionId();
            String planMissionId = IdGenerator.generateMd5Id("", planId + missionId);
            planMission.setId(planMissionId);
            if(problem != null){
                if(problem != null){
                    if(MyStringUtil.isNullOrEmpty(problem.getId())){
                        String problemId = IdGenerator.generateMd5Id("problem", planId + missionId);
                        problem.setId(problemId);
                        //problem.setId(IdGenerator.generateDateTimeId(planId+"_"+missionId+"_problem", new Date()));
                        problem.setPass(0);
                    }
                }
                session.saveOrUpdate(problem);
                planMission.setProblemId(problem.getId()+"");
            }else {
                String delete_sql = "delete from csvw_skr_task_problem where planId=?  and  missionId= ? ";
                SQLQuery q = session.createSQLQuery(delete_sql);
                q.setParameter(0,planId);
                q.setParameter(1,missionId);
                q.executeUpdate();
            }
            session.saveOrUpdate(planMission);

            Mission mission = session.get(Mission.class,missionId);
            if (mission.isChecklist()){
                List<String> checkIds = visitMissionReportDto.getCheckIds();
                if (checkIds != null && checkIds.size() > 0) {
                    //先删除历史的
                    //String delete_sql = "delete from csvw_skr_task_visit_mission_check t1 where t1.visitId = ? and t1.missionId = ? ";
                    String delete_sql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = false where t1.visitId = ? and t1.missionId = ? ";
                    SQLQuery delete = session.createSQLQuery(delete_sql);
                    delete.setParameter(0, planId);
                    delete.setParameter(1, missionId);
                    delete.executeUpdate();

                    //再插入新的
                    int lenght = checkIds.size();
                    for (int i = 0; i < lenght; i++) {
                        String sql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = true where t1.checkId = ? and t1.visitId = ? and t1.missionId = ? ";
                        SQLQuery q = session.createSQLQuery(sql);
                        q.setParameter(0, checkIds.get(i).trim());
                        q.setParameter(1, planId);
                        q.setParameter(2, missionId);
                        q.executeUpdate();
                    }
                }
            }
            tx.commit();
            return planMission;
        }catch (Exception e){
            tx.rollback();
            return null;
        }finally {
            if (session != null){
                session.close();
            }
        }
    }

    /**
     * 提交店访任务检查表
     * @param visitId
     * @param missionId
     * @param checkIds
     * @return
     */
    public boolean reportPlanMissionCheck(String visitId, String missionId, @NotNull List<String> checkIds) {
        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();
        try {
            String initSql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = false where t1.visitId = ? and t1.missionId = ? ";
            SQLQuery q1 = session.createSQLQuery(initSql);
            q1.setParameter(0, visitId);
            q1.setParameter(1, missionId);
            q1.executeUpdate();

            int lenght = checkIds.size();
            for (int i = 0; i < lenght; i++) {
                String sql = "update csvw_skr_task_visit_mission_check t1 set t1.pass = true where t1.checkId = ? and t1.visitId = ? and t1.missionId = ? ";
                SQLQuery q = session.createSQLQuery(sql);
                q.setParameter(0, checkIds.get(i).trim());
                q.setParameter(1, visitId);
                q.setParameter(2, missionId);
                q.executeUpdate();
            }
            tx.commit();
            return true;
        } catch (Exception e) {
            tx.rollback();
            return false;
        } finally {
            if (session != null)
                session.close();
        }
    }

    /**
     * 获取走访项
     * @param entityId
     * @return
     */
    public Mission getMission(String entityId) {
        return myDao.get(entityId,Mission.class);
    }

    /**
     *获取店访任务检查表检查项
     * @param visitId
     * @param missionId
     * @return
     */
    public List<Map<String, Object>> getPlanMissionCheck(String visitId, String missionId) {
        String sql = "select visit_check.pk_id as id, name, description, mission_check.pass from csvw_skr_task_visit_check visit_check " +
                "left join csvw_skr_task_visit_mission_check mission_check on mission_check.checkId = visit_check.pk_id " +
                "where mission_check.visitId=?  and mission_check.missionId =? order by cast(visit_check.pk_id as signed) asc ";
        List<Map<String, Object>> result = myDao.queryResultToMap(sql, new Object[]{visitId, missionId});
        return result;
    }

    /**
     *获取店访计划详情
     * @param visitId
     * @return
     */
    public Map<String, Object> getVisitPlanInfo(@NotNull String visitId) {
        Map<String, Object> result = new HashMap<>();

        VisitPlan visitPlan = myDao.get(visitId, VisitPlan.class);

        Map<String, Object> visit = JavaBeanToMap.transBean2Map(visitPlan);

        Dealer dealer = myDao.get((String) visit.getOrDefault("dealer", ""), Dealer.class);

        if (dealer == null) {
            visit.put("dealerName", "");
        } else {
            visit.put("dealerName", dealer.getShorter());
        }

        result.put("visitPlan", visit);
        //获取走访项
        String mission_sql = "select mission.pk_id as id, mission.name, mission.description, mission.type, plan_mission.finish, mission.checklist from csvw_skr_task_visit_mission mission " +
                "left join csvw_skr_task_visit_plan_mission plan_mission on plan_mission.missionId = mission.pk_id " +
                "where mission.active = true and plan_mission.visitId=? ";
        List<Map<String, Object>> missions = myDao.queryResultToMap(mission_sql, new Object[]{visitId});
        if (null == missions || missions.size() < 1) {
            missions = Lists.newArrayList();
        }
        result.put("missions", missions);

        return result;
    }
}
